﻿using System;
using System.Data;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Skyworth.CRM.DAL
{
    public class CustomerDAL : SuperDataBase, ICustomerDAL
    {
        public void Add(Customer model, List<CustContact> listContact, List<CustDree> listAdd)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into T_BasicData_Customer(");
                strSql.Append("FNumber,FName,FDreeId,FDree,FSalesId,FMobil,FTel,FQH,FJB,FCustBrand,FCustMarket,");
                strSql.Append("FProducts,FChannels,FWebAddress)");
                strSql.Append(" values (");
                strSql.Append("@FNumber,@FName,@FDreeId,@FDree,@FSalesId,@FMobil,@FTel,@FQH,@FJB,@FCustBrand,@FCustMarket,");
                strSql.Append("@FProducts,@FChannels,@FWebAddress)");
                strSql.Append(";select @NewID=@@IDENTITY");
                using (TransactionScope ts = new TransactionScope())
                {
                    DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                    DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                    DB.AddInParameter(cmd, "@FName", DbType.String, model.FName);
                    DB.AddInParameter(cmd, "@FDreeId", DbType.Int32, model.FDreeId);
                    DB.AddInParameter(cmd, "@FDree", DbType.String, model.FDree);
                    DB.AddInParameter(cmd, "@FSalesId", DbType.Int32, model.FSalesId);
                    DB.AddInParameter(cmd, "@FMobil", DbType.String, model.FMobil);
                    DB.AddInParameter(cmd, "@FTel", DbType.String, model.FTel);
                    DB.AddInParameter(cmd, "@FQH", DbType.String, model.FQH);
                    DB.AddInParameter(cmd, "@FJB", DbType.Int32, model.FJB);
                    DB.AddInParameter(cmd, "@FCustBrand", DbType.String, model.CustBrand);
                    DB.AddInParameter(cmd, "@FCustMarket", DbType.String, model.CustMarket);
                    DB.AddInParameter(cmd, "@FProducts", DbType.String, model.Products);
                    DB.AddInParameter(cmd, "@FChannels", DbType.String, model.Channels);
                    DB.AddInParameter(cmd, "@FWebAddress", DbType.String, model.WebAddress);
                    DB.AddOutParameter(cmd, "@NewID", DbType.Int32, 32);
                    DB.ExecuteNonQuery(cmd);
                    int custId = Convert.ToInt32(DB.GetParameterValue(cmd, "@NewID"));
                    // 联系人
                    StringBuilder sqlContact = new StringBuilder();
                    sqlContact.Append("insert into T_BasicData_Contact(");
                    sqlContact.Append("FCustId,FName,FIsD,FTel,FMobile,FEmail,FHobby,FDree,FNote)");
                    sqlContact.Append(" values (");
                    sqlContact.Append("@FCustId,@FName,@FIsD,@FTel,@FMobile,@FEmail,@FHobby,@FDree,@FNote)");
                    for (int i = 0; i < listContact.Count; i++)
                    {
                        cmd = DB.GetSqlStringCommand(sqlContact.ToString());
                        DB.AddInParameter(cmd, "@FCustId", DbType.Int32, custId);
                        DB.AddInParameter(cmd, "@FName", DbType.String, listContact[i].FName);
                        DB.AddInParameter(cmd, "@FIsD", DbType.Boolean, listContact[i].FIsD);
                        DB.AddInParameter(cmd, "@FTel", DbType.String, listContact[i].FTel);
                        DB.AddInParameter(cmd, "@FMobile", DbType.String, listContact[i].FMobile);
                        DB.AddInParameter(cmd, "@FEmail", DbType.String, listContact[i].FEmail);
                        DB.AddInParameter(cmd, "@FHobby", DbType.String, listContact[i].FHobby);
                        DB.AddInParameter(cmd, "@FDree", DbType.String, listContact[i].FDree);
                        DB.AddInParameter(cmd, "@FNote", DbType.String, listContact[i].FNote);
                        DB.ExecuteNonQuery(cmd);
                    }
                    // 收货人
                    StringBuilder sqlAdd = new StringBuilder();
                    sqlAdd.Append("insert into T_BasicData_CusDree(");
                    sqlAdd.Append("FCustId,FDreeName,FNumber,FName,FTel,FMobile)");
                    sqlAdd.Append(" values (");
                    sqlAdd.Append("@FCustId,@FDreeName,@FNumber,@FName,@FTel,@FMobile)");
                    for (int j = 0; j < listAdd.Count; j++)
                    {
                        cmd = DB.GetSqlStringCommand(sqlAdd.ToString());
                        DB.AddInParameter(cmd, "@FCustId", DbType.Int32, custId);
                        DB.AddInParameter(cmd, "@FDreeName", DbType.String, listAdd[j].FDreeName);
                        DB.AddInParameter(cmd, "@FNumber", DbType.Int32, listAdd[j].FNumber);
                        DB.AddInParameter(cmd, "@FName", DbType.String, listAdd[j].FName);
                        DB.AddInParameter(cmd, "@FTel", DbType.String, listAdd[j].FTel);
                        DB.AddInParameter(cmd, "@FMobile", DbType.String, listAdd[j].FMobile);
                        DB.ExecuteNonQuery(cmd);
                    }
                    // 客户可用金额
                    StringBuilder sqlCash = new StringBuilder();
                    sqlCash.AppendFormat("insert into [T_Cash](Fcustid,FCashAmount,FcreditAmount,FTotalAmount) values({0},0,0,0)", custId);
                    cmd = DB.GetSqlStringCommand(sqlCash.ToString());
                    DB.ExecuteNonQuery(cmd);
                    // 仓库
                    StringBuilder sqlStock = new StringBuilder();
                    sqlStock.AppendFormat("insert into T_BasicData_Stock(FNumber,FName,FType,FDeptId,FStatus) values('000000','{0}',1,{1},1)", model.FName, custId);
                    cmd = DB.GetSqlStringCommand(sqlStock.ToString());
                    DB.ExecuteNonQuery(cmd);
                    ts.Complete();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(Customer model, List<CustContact> listContact, List<CustDree> listAdd)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_BasicData_Customer set ");
                strSql.Append("FNumber=@FNumber,");
                strSql.Append("FName=@FName,");
                strSql.Append("FDreeId=@FDreeId,");
                strSql.Append("FDree=@FDree,");
                strSql.Append("FSalesId=@FSalesId,");
                strSql.Append("FMobil=@FMobil,");
                strSql.Append("FTel=@FTel,");
                strSql.Append("FQH=@FQH,");
                strSql.Append("FJB=@FJB,");
                strSql.Append("FCustBrand=@FCustBrand,");
                strSql.Append("FCustMarket=@FCustMarket,");
                strSql.Append("FProducts=@FProducts,");
                strSql.Append("FChannels=@FChannels,");
                strSql.Append("FWebAddress=@FWebAddress");
                strSql.Append(" where FItemId=@FItemId");
                using (TransactionScope ts = new TransactionScope())
                {
                    DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                    DB.AddInParameter(cmd, "@FItemId", DbType.Int32, model.FItemId);
                    DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                    DB.AddInParameter(cmd, "@FName", DbType.String, model.FName);
                    DB.AddInParameter(cmd, "@FDreeId", DbType.Int32, model.FDreeId);
                    DB.AddInParameter(cmd, "@FDree", DbType.String, model.FDree);
                    DB.AddInParameter(cmd, "@FSalesId", DbType.Int32, model.FSalesId);
                    DB.AddInParameter(cmd, "@FMobil", DbType.String, model.FMobil);
                    DB.AddInParameter(cmd, "@FTel", DbType.String, model.FTel);
                    DB.AddInParameter(cmd, "@FQH", DbType.String, model.FQH);
                    DB.AddInParameter(cmd, "@FJB", DbType.Int32, model.FJB);
                    DB.AddInParameter(cmd, "@FCustBrand", DbType.String, model.CustBrand);
                    DB.AddInParameter(cmd, "@FCustMarket", DbType.String, model.CustMarket);
                    DB.AddInParameter(cmd, "@FProducts", DbType.String, model.Products);
                    DB.AddInParameter(cmd, "@FChannels", DbType.String, model.Channels);
                    DB.AddInParameter(cmd, "@FWebAddress", DbType.String, model.WebAddress);
                    DB.ExecuteNonQuery(cmd);
                    // 删除
                    string delContact = string.Format("delete from T_BasicData_Contact where FCustId = {0}",model.FItemId);
                    cmd = DB.GetSqlStringCommand(delContact);
                    DB.ExecuteNonQuery(cmd);
                    string delAdd = string.Format("delete from T_BasicData_CusDree where FCustId = {0}",model.FItemId);
                    cmd = DB.GetSqlStringCommand(delAdd);
                    DB.ExecuteNonQuery(cmd);
                    // 联系人
                    StringBuilder sqlContact = new StringBuilder();
                    sqlContact.Append("insert into T_BasicData_Contact(");
                    sqlContact.Append("FCustId,FName,FIsD,FTel,FMobile,FEmail,FHobby,FDree,FNote)");
                    sqlContact.Append(" values (");
                    sqlContact.Append("@FCustId,@FName,@FIsD,@FTel,@FMobile,@FEmail,@FHobby,@FDree,@FNote)");
                    for (int i = 0; i < listContact.Count; i++)
                    {
                        cmd = DB.GetSqlStringCommand(sqlContact.ToString());
                        DB.AddInParameter(cmd, "@FCustId", DbType.Int32, model.FItemId);
                        DB.AddInParameter(cmd, "@FName", DbType.String, listContact[i].FName);
                        DB.AddInParameter(cmd, "@FIsD", DbType.Boolean, listContact[i].FIsD);
                        DB.AddInParameter(cmd, "@FTel", DbType.String, listContact[i].FTel);
                        DB.AddInParameter(cmd, "@FMobile", DbType.String, listContact[i].FMobile);
                        DB.AddInParameter(cmd, "@FEmail", DbType.String, listContact[i].FEmail);
                        DB.AddInParameter(cmd, "@FHobby", DbType.String, listContact[i].FHobby);
                        DB.AddInParameter(cmd, "@FDree", DbType.String, listContact[i].FDree);
                        DB.AddInParameter(cmd, "@FNote", DbType.String, listContact[i].FNote);
                        DB.ExecuteNonQuery(cmd);
                    }
                    // 收货人
                    StringBuilder sqlAdd = new StringBuilder();
                    sqlAdd.Append("insert into T_BasicData_CusDree(");
                    sqlAdd.Append("FCustId,FDreeName,FNumber,FName,FTel,FMobile)");
                    sqlAdd.Append(" values (");
                    sqlAdd.Append("@FCustId,@FDreeName,@FNumber,@FName,@FTel,@FMobile)");
                    for (int j = 0; j < listAdd.Count; j++)
                    {
                        cmd = DB.GetSqlStringCommand(sqlAdd.ToString());
                        DB.AddInParameter(cmd, "@FCustId", DbType.Int32, model.FItemId);
                        DB.AddInParameter(cmd, "@FDreeName", DbType.String, listAdd[j].FDreeName);
                        DB.AddInParameter(cmd, "@FNumber", DbType.Int32, listAdd[j].FNumber);
                        DB.AddInParameter(cmd, "@FName", DbType.String, listAdd[j].FName);
                        DB.AddInParameter(cmd, "@FTel", DbType.String, listAdd[j].FTel);
                        DB.AddInParameter(cmd, "@FMobile", DbType.String, listAdd[j].FMobile);
                        DB.ExecuteNonQuery(cmd);
                    }
                    ts.Complete();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int FItemId)
        {
            try
            {
                StringBuilder sqlAdd = new StringBuilder();
                sqlAdd.Append("delete from T_BasicData_CusDree where FCustId = @FCustId");
                StringBuilder sqlContact = new StringBuilder();
                sqlContact.Append("delete from T_BasicData_Contact where FCustId = @FCustId");
                StringBuilder sqlCust = new StringBuilder();
                sqlCust.Append("delete from T_BasicData_Customer where FItemId=@FItemId");
                using (TransactionScope ts = new TransactionScope())
                {
                    // 删除收货地址
                    DbCommand cmd = DB.GetSqlStringCommand(sqlAdd.ToString());
                    DB.AddInParameter(cmd, "@FCustId", DbType.Int32, FItemId);
                    DB.ExecuteNonQuery(cmd);
                    // 删除联系人
                    cmd = DB.GetSqlStringCommand(sqlContact.ToString());
                    DB.AddInParameter(cmd, "@FCustId", DbType.Int32, FItemId);
                    DB.ExecuteNonQuery(cmd);
                    // 删除客户
                    cmd = DB.GetSqlStringCommand(sqlCust.ToString());
                    DB.AddInParameter(cmd, "@FItemId", DbType.Int32, FItemId);
                    DB.ExecuteNonQuery(cmd);
                    ts.Complete();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Customer GetModel(int fItemId)
        {
            try
            {
                Customer model = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 FItemId,FNumber,FName,FDreeId,FDree,FSalesId,FMobil,FTel,FQH,FJB,FCustBrand,FCustMarket,");
                strSql.Append("FProducts,FChannels,FWebAddress from T_BasicData_Customer ");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, fItemId);
                using (IDataReader dr = DB.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        model = new Customer();
                        if (dr["FItemId"].ToString() != "")
                        {
                            model.FItemId = int.Parse(dr["FItemId"].ToString());
                        }
                        model.FNumber = dr["FNumber"].ToString();
                        model.FName = dr["FName"].ToString();
                        if (dr["FDreeId"].ToString() != "")
                        {
                            model.FDreeId = int.Parse(dr["FDreeId"].ToString());
                        }
                        model.FDree = dr["FDree"].ToString();
                        if (dr["FSalesId"].ToString() != "")
                        {
                            model.FSalesId = int.Parse(dr["FSalesId"].ToString());
                        }
                        model.FMobil = dr["FMobil"].ToString();
                        model.FTel = dr["FTel"].ToString();
                        model.FQH = dr["FQH"].ToString();
                        if (dr["FJB"].ToString() != "")
                        {
                            model.FJB = int.Parse(dr["FJB"].ToString());
                        }
                        model.CustBrand = dr["FCustBrand"].ToString();
                        model.CustMarket = dr["FCustMarket"].ToString();
                        model.Products = dr["FProducts"].ToString();
                        model.Channels = dr["FChannels"].ToString();
                        model.WebAddress = dr["FWebAddress"].ToString();
                    }
                }
                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool Exists(string custCode, out int custId)
        {
            try
            {
                custId = -1;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select top 1 FItemId from T_BasicData_Customer");
                strSql.Append(" where FNumber=@FNumber");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FNumber", DbType.String, custCode);
                object obj = DB.ExecuteScalar(cmd);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return false;
                }
                else
                {
                    custId = int.Parse(obj.ToString());
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Query(string strWhere)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select FItemId,FNumber,FName,FDreeId,FDree,FSalesId,FMobil,FTel,FQH,FJB,FCustBrand,FCustMarket,");
                strSql.Append("FProducts,FChannels,FWebAddress,EmpName,");
                strSql.Append("case FJB when 1 then 'A' when 2 then 'B' when 3 then 'C' when 4 then 'D' end as LevelName");
                strSql.Append(" FROM T_BasicData_Customer left join T_BasicData_Employee as tbe on T_BasicData_Customer.FSalesId=tbe.EmpId where 1=1");
                if (strWhere.Trim() != "")
                {
                    //strSql.Append(" where " + strWhere);
                    strSql.Append( strWhere);
                }
                DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetCustomerssByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
        {
            recordCount = 0;
            pageCount = 0;
            StringBuilder strCommandText = new StringBuilder();
            DataSet ds;
            strCommandText.AppendFormat(
                   "SELECT row_number() over({0}) AS rownum, FItemId,FNumber,FName,FDreeId,FDree,FSalesId,FMobil,FTel,FQH,FJB,FCustBrand,FCustMarket,FProducts,FChannels,FWebAddress,EmpName,", strOrderBy);
            strCommandText.Append("case FJB when 1 then 'A' when 2 then 'B' when 3 then 'C' when 4 then 'D' end as LevelName");
            strCommandText.Append(" FROM T_BasicData_Customer left join T_BasicData_Employee as tbe on T_BasicData_Customer.FSalesId=tbe.EmpId ");
            strCommandText.Append(strWhere);
            System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
            DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
            DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
            DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
            DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
            DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
            DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
            DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
            ds = DB.ExecuteDataSet(sqlcommand);
            recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
            pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

            return ds.Tables[0];
        }

        public void DeletePriceCust(int policyId, int custId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_Sales_PriceCust ");
            strSql.Append(" where CustId=@custId and PolicyId=@policyId");
            DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
            DB.AddInParameter(cmd, "@custId", DbType.Int32, custId);
            DB.AddInParameter(cmd, "@policyId", DbType.Int32, policyId);
            DB.ExecuteNonQuery(cmd);
        }

        public void AddPriceCust(int policyId, int custId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" insert into T_Sales_PriceCust(PolicyId,CustId)  ");
            strSql.Append(" values(@policyId,@custId)");
            DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
            DB.AddInParameter(cmd, "@custId", DbType.Int32, custId);
            DB.AddInParameter(cmd, "@policyId", DbType.Int32, policyId);
            DB.ExecuteNonQuery(cmd);
        }

       
    }
}